﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using K3R.Generics;

namespace K3R.Data {
	public class Connection : IDisposable {
		protected string _connString = "";
		protected SqlConnection _conn = null;
		protected SqlCommand _cmd = null;
		protected CommandBehavior _behavior = CommandBehavior.Default;

		public SqlParameterCollection Parameters {
			get {
				SqlParameterCollection col = null;
				if (_cmd != null)
					col = _cmd.Parameters;
				return col;
			}
		}

		#region Constructors
		public Connection(string connString, string cmdText, CommandBehavior behavior, SqlParameter[] parameters) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
			_cmd.Parameters.AddRange(parameters);
			_behavior = behavior;
		}

		public Connection(string connString, string cmdText, CommandBehavior behavior, SqlParameter parameter) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
			_cmd.Parameters.Add(parameter);
			_behavior = behavior;
		}

		public Connection(string connString, string cmdText, SqlParameter[] parameters) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
			_cmd.Parameters.AddRange(parameters);
		}

		public Connection(string connString, string cmdText, SqlParameter parameter) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
			_cmd.Parameters.Add(parameter);
		}

		public Connection(string connString, string cmdText, CommandBehavior behavior) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
			_behavior = behavior;
		}

		public Connection(string connString, string cmdText) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
		}

		public Connection(string connString, string cmdText, bool isText) {
			_connString = connString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = (isText ? CommandType.Text : CommandType.StoredProcedure);
		}

		public Connection(string cmdText) {
			_connString = Settings.ConnectionString;
			_conn = new SqlConnection(_connString);
			_cmd = new SqlCommand(cmdText, _conn);
			_cmd.CommandType = CommandType.StoredProcedure;
		}
		#endregion

		#region Parameters
		public SqlParameter AddParameter(SqlParameter parameter) {
			SqlParameter ret = null;

			if (_cmd != null)
				ret = _cmd.Parameters.Add(parameter);

			return ret;
		}

		public void AddParameter(SqlParameter[] parameters) {
			if (_cmd != null)
				_cmd.Parameters.AddRange(parameters);
		}

		public SqlParameter AddParameter(string parameterName, object value) {
			SqlParameter param = null;

			if (value != null)
				param = _cmd.Parameters.AddWithValue(parameterName, value);

			return param;
		}

		public SqlParameter AddParameter<T>(string parameterName, Nullable<T> value) where T : struct {
			SqlParameter param = null;

			if (value.HasValue)
				param = _cmd.Parameters.AddWithValue(parameterName, value.Value);

			return param;
		}

		public SqlParameter AddParameter(string parameterName, SqlDbType sqlDbType, ParameterDirection direction) {
			SqlParameter parameter = _cmd.Parameters.Add(parameterName, sqlDbType);
			parameter.Direction = direction;
			return parameter;
		}

		public SqlParameter AddParameter(string parameterName, SqlDbType sqlDbType, int size, ParameterDirection direction) {
			SqlParameter parameter = _cmd.Parameters.Add(parameterName, sqlDbType, size);
			parameter.Direction = direction;
			return parameter;
		}

		public SqlParameter AddOutputParameter(string parameterName, SqlDbType sqlDbType) {
			return AddParameter(parameterName, sqlDbType, ParameterDirection.Output);
		}

		public SqlParameter AddOutputID(string parameterName) {
			return AddOutputParameter(parameterName, SqlDbType.Int);
		}

		public SqlParameter AddOutputID() {
			return AddOutputID("@id");
		}

		public SqlParameter AddOutputGUID(string parameterName) {
			return AddOutputParameter(parameterName, SqlDbType.UniqueIdentifier);
		}

		public SqlParameter AddOutputGUID() {
			return AddOutputGUID("@guid");
		}
		#endregion

		#region Executions
		public SqlDataReader ExecuteReader(CommandBehavior behavior) {
			if (_conn.State == ConnectionState.Closed)
				_conn.Open();

			return _cmd.ExecuteReader(behavior);
		}

		public SqlDataReader ExecuteReader() {
			return ExecuteReader(_behavior);
		}

		public T ExecuteReader<T>() {
			SqlDataReader dr = ExecuteReader(CommandBehavior.SingleResult);
			T result = default(T);

			if (dr.Read())
				result = dr[0].DBValueOrDefault<T>();

			return result;
		}

		public object ExecuteScalar() {
			if (_conn.State == ConnectionState.Closed)
				_conn.Open();

			return _cmd.ExecuteScalar();
		}

		public int ExecuteNonQuery() {
			if (_conn.State == ConnectionState.Closed)
				_conn.Open();

			return _cmd.ExecuteNonQuery();
		}

		public T ExecuteNonQuery<T>(string parameterName, SqlDbType sqlDbType) {
			if (!_cmd.Parameters.Contains(parameterName))
				AddOutputParameter(parameterName, sqlDbType);

			ExecuteNonQuery();
			return _cmd.Parameters[parameterName].DBValueOrDefault<T>();
		}

		public T ExecuteNonQuery<T>(string parameterName) {
			return ExecuteNonQuery<T>(parameterName, SqlDbType.Int);
		}

		public T ExecuteNonQuery<T>() {
			return ExecuteNonQuery<T>("@id");
		}

		public List<T> ToList<T>() {
			return ExecuteReader().ToList<T>();
		}

		public Dictionary<K, V> ToDictionary<K, V>() {
			return ExecuteReader().ToDictionary<K, V>();
		}

		public List<SerializableKeyValuePair<K, V>> ToSerializableKeyValuePairList<K, V>() {
			return ExecuteReader().ToSerializableKeyValuePairList<K, V>();
		}
		#endregion

		#region IDisposable Members

		public void Dispose() {
			if (_cmd != null) {
				_cmd.Dispose();
				_cmd = null;
			}

			if (_conn != null) {
				_conn.Dispose();
				_conn = null;
			}
		}

		#endregion
	}
}
